package apollo.generator.common.utils;

import apollo.generator.common.constant.GeneratorConstant;
import apollo.generator.modules.entity.ColumnEntity;
import apollo.generator.modules.entity.DataBaseConfigEntity;
import apollo.generator.modules.entity.TableEntity;
import apollo.generator.modules.entity.vo.DataBasePageVO;
import cn.hutool.core.util.ObjectUtil;
import com.google.common.collect.Lists;
import lombok.Data;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

/**
 * <Description>
 * 数据库工具类
 * @author Jun Lee
 * @version 1.0
 * @createDate 2020/07/02 16:03
 * @see apollo.generator.common.utils
 */
@Component
public class DataBaseUtils {

    /**
     * 获取元数据库链接
     * @param db
     * @return
     * @throws Exception
     */
    public static Connection getMetaConnection(DataBaseConfigEntity db) throws Exception {
        Properties props = new Properties();
        props.put("remarksReporting","true");
        props.put("user",db.getUsername());
        props.put("password",db.getPassword());
        Class.forName(db.getDataBaseDrive());
        String url = String.format(GeneratorConstant.MATE_URL, db.getDataBaseIp(), db.getPort());
        return DriverManager.getConnection(url,props);
    }

    /**
     * 获取数据库链接
     * @param db
     * @return
     * @throws Exception
     */
    public static Connection getConnection(DataBaseConfigEntity db,String dbName) throws Exception {
        Properties props = new Properties();
        props.put("remarksReporting","true");
        props.put("user",db.getUsername());
        props.put("password",db.getPassword());
        Class.forName(db.getDataBaseDrive());
        String url = String.format(GeneratorConstant.COMMON_URL, db.getDataBaseIp(), db.getPort(), dbName);
        return DriverManager.getConnection(url,props);
    }


    /**
     * 通用查询
     * @param db
     * @return
     * @throws Exception
     */
    public static DataAttributeObject query(DataBaseConfigEntity db, String sql,Integer flag,String dbName) throws Exception {
        Connection connection = null;
        if (GeneratorConstant.MATE_FLAG.equals(flag)) {
            connection = getMetaConnection(db);
        }else{
            connection = getConnection(db,dbName);
        }
        Statement statement = connection.createStatement();
        ResultSet set = statement.executeQuery(sql);
        return new DataAttributeObject(connection,statement,set);
    }

    /**
     * 获取数据库列表
     *
     * @return
     * @throws Exception
     */
    public static ArrayList<DataBasePageVO> getDataBasePages(DataBaseConfigEntity db,String key) throws Exception {
        String querySql = "SELECT SCHEMA_NAME schemaName, DEFAULT_CHARACTER_SET_NAME characterName, DEFAULT_COLLATION_NAME collationName FROM information_schema.SCHEMATA";
        if (ObjectUtil.isNotNull(key)) {
            querySql = querySql.concat("  where SCHEMA_NAME like '%"+key+"%'");
        }
        DataAttributeObject object = query(db, querySql , GeneratorConstant.MATE_FLAG , null);
        ResultSet resultSet = object.getResultSet();
        ArrayList<DataBasePageVO> objects = Lists.newArrayList();
        while (resultSet.next()) {
            DataBasePageVO vo = new DataBasePageVO();
            vo.setSchemaName(resultSet.getString("schemaName"))
                    .setCharacterName(resultSet.getString("characterName"))
                    .setCollationName(resultSet.getString("collationName"));
            if (vo.getSchemaName().equals("information_schema") || vo.getSchemaName().equals("performance_schema")) {
                continue;
            }
            objects.add(vo);
        }
        close(object);
        return objects;
    }


    /**
     * 获取数据库表
     *
     * @return
     * @throws Exception
     */
    public static ArrayList<TableEntity> selectTablePage(DataBaseConfigEntity db, String dataBaseName , String key) throws Exception {
        String querySql = "SELECT table_name tableName,ENGINE,table_comment tableComment,create_time createTime FROM information_schema. TABLES WHERE table_schema = (SELECT DATABASE())";
        if (StringUtils.isNotEmpty(key)) {
            querySql = querySql.concat("  AND table_name like '%"+key+"%'");
        }
        DataAttributeObject object = query(db, querySql ,GeneratorConstant.COMMON_FLAG , dataBaseName);
        ResultSet resultSet = object.getResultSet();
        ArrayList<TableEntity> objects = Lists.newArrayList();
        while (resultSet.next()) {
            TableEntity vo = new TableEntity();
            vo.setTableName(resultSet.getString("tableName"));
            vo.setTableComment(resultSet.getString("tableComment"));
            vo.setCreateTime(resultSet.getString("createTime"));
            vo.setENGINE(resultSet.getString("ENGINE"));
            objects.add(vo);
        }
        close(object);
        return objects;
    }


    /**
     * 获取单个数据库表信息
     *
     * @return
     * @throws Exception
     */
    public static TableEntity getOneTable(DataBaseConfigEntity db, String dataBaseName , String tableName) throws Exception {
        String querySql = "SELECT table_name tableName,ENGINE,table_comment tableComment,create_time createTime FROM information_schema. TABLES WHERE table_schema = (SELECT DATABASE())" +
                "AND table_name = '"+tableName+"'";
        DataAttributeObject object = query(db, querySql ,GeneratorConstant.COMMON_FLAG , dataBaseName);
        ResultSet resultSet = object.getResultSet();
        TableEntity entity = new TableEntity();
        while (resultSet.next()) {
            entity.setTableName(resultSet.getString("tableName"));
            entity.setTableComment(resultSet.getString("tableComment"));
            entity .setCreateTime(resultSet.getString("createTime"));
            entity.setENGINE(resultSet.getString("ENGINE"));
        }
        close(object);
        return entity;
    }


    /**
     * 获取数据库表的列
     *
     * @return
     * @throws Exception
     */
    public static ArrayList<ColumnEntity> selectColumns(DataBaseConfigEntity db, String dataBaseName , String tableName) throws Exception {
        String querySql = "select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns\n" +
                " \t\t\twhere table_name = '"+tableName+"' and table_schema = (select database()) order by ordinal_position";
        DataAttributeObject object = query(db, querySql ,GeneratorConstant.COMMON_FLAG , dataBaseName);
        ResultSet resultSet = object.getResultSet();
        ArrayList<ColumnEntity> objects = Lists.newArrayList();
        while (resultSet.next()) {
            ColumnEntity vo = new ColumnEntity();
            vo.setColumnName(resultSet.getString("columnName"));
            vo.setColumnComment(resultSet.getString("columnComment"));
            vo.setColumnKey(StringUtils.isNotBlank(resultSet.getString("columnKey")));
            vo.setDataType(resultSet.getString("dataType"));
            objects.add(vo);
        }
        close(object);
        return objects;
    }

    /**
     * 关闭资源
     * @param object
     * @throws SQLException
     */
    public static void close(DataAttributeObject object) throws SQLException {
        if(object.getStatement()!=null){
            object.getStatement().close();
        }
        if(object.getConnection()!=null){
            object.getConnection().close();
        }
        if(object.getResultSet()!=null){
            object.getResultSet().close();
        }
    }

    @Data
    static
    class DataAttributeObject {
        private Connection connection;
        private Statement statement;
        private ResultSet resultSet;

        public DataAttributeObject(Connection connection, Statement statement, ResultSet resultSet) {
            this.connection = connection;
            this.statement = statement;
            this.resultSet = resultSet;
        }
    }
}
